<?php

namespace SLN0002\DAO;

use Home\DAO\BizConfigDAO;
use Home\DAO\PSIBaseExDAO;

/**
 * 账簿查询 DAO
 *
 * @author PSI
 * @copyright 2015 - present
 * @license GPL v3
 */
class AccQueryDAO extends PSIBaseExDAO
{

  /**
   * 查询明细账簿列表
   */
  public function queryAccList($params)
  {
    $db = $this->db;

    $companyId = $params["companyId"];
    $subjectCode = $params["subjectCode"];

    $queryParams = [$companyId];
    $sql = "select id, acc_number, subject_code
            from t_acc_fmt
            where company_id = '%s' and in_use = 1";

    if ($subjectCode) {
      $sql .= " and subject_code like '%s' ";
      $queryParams[] = "%{$subjectCode}%";
    }

    $sql .= " order by acc_number";
    $data = $db->query($sql, $queryParams);

    $result = [];

    foreach ($data as $v) {
      $fmtId = $v["id"];
      $subjectCode = $v["subject_code"];

      $sql = "select count(db_field_name) as cnt
              from t_acc_fmt_cols
              where fmt_id = '%s' and sub_acc_level between 1 and 3";
      $d = $db->query($sql, $fmtId);
      $cnt = $d[0]["cnt"];
      if ($cnt === 0) {
        $cnt = -1;
      }

      $sql = "select name
              from t_subject
              where code = '%s' ";
      $d = $db->query($sql, $subjectCode);
      $name = $d[0]["name"];

      $result[] = [
        "id" => $fmtId,
        "accNumber" => $v["acc_number"],
        "name" => $name,
        "subjectCode" => $subjectCode,
        "subAccLevel" => $cnt,
      ];
    }

    return $result;
  }

  /**
   * 查询一级子账簿
   */
  public function querySubAcc01($params)
  {
    $db = $this->db;

    $companyId = $params["companyId"];
    $accNumber = $params["accNumber"];
    $year = $params["year"];
    $month = $params["month"];

    $start = $params["start"];
    $limit = $params["limit"];

    $sql = "select id, db_table_name_prefix
            from t_acc_fmt
            where company_id = '%s' and acc_number = '%s' ";
    $data = $db->query($sql, $companyId, $accNumber);
    if (!$data) {
      // 莫名的bug
      return [
        "dataList" => [],
        "totalCount" => 0,
      ];
    }
    $v = $data[0];
    $fmtId = $v["id"];
    $tableName = $v["db_table_name_prefix"] . "_detail";

    $sql = "select db_field_name, voucher_input
            from t_acc_fmt_cols
            where fmt_id = '%s' and sub_acc_level = 1
            limit 1";
    $data = $db->query($sql, $fmtId);
    if (!$data) {
      // 莫名的bug
      return [
        "dataList" => [],
        "totalCount" => 0,
      ];
    }

    $v = $data[0];
    $fieldName = $v["db_field_name"];
    $fieldList = $fieldName;
    $voucherInput = $v["voucher_input"];
    if ($voucherInput == 2) {
      // 码表录入
      $fieldList .= ", {$fieldName}_name";
    }
    $sql = "select distinct {$fieldList} from {$tableName}
            where company_id = '%s' and voucher_year = %d and voucher_month = %d
              and sub_acc_level = 1
            order by {$fieldName} 
            limit %d , %d";
    $data = $db->query($sql, $companyId, $year, $month, $start, $limit);
    $result = [
      [
        "code" => "",
        "name" => "[忽略一级子账簿]",
      ],
    ];
    foreach ($data as $v) {
      $n = $v[$fieldName];
      if ($voucherInput == 2) {
        $n = $v[$fieldName] . " - " . $v[$fieldName . "_name"];
      }
      $result[] = [
        "code" => $v[$fieldName],
        "name" => $n,
      ];
    }

    $sql = "select count(distinct {$fieldName}) as cnt from {$tableName}
            where company_id = '%s' and voucher_year = %d and voucher_month = %d
              and sub_acc_level = 1";
    $data = $db->query($sql, $companyId, $year, $month);
    $cnt = $data[0]["cnt"];
    return [
      "dataList" => $result,
      "totalCount" => $cnt,
    ];
  }

  /**
   * 查询二级子账簿
   */
  public function querySubAcc02($params)
  {
    $db = $this->db;

    $companyId = $params["companyId"];
    $accNumber = $params["accNumber"];
    $year = $params["year"];
    $month = $params["month"];
    $subAcc01 = $params["subAcc01"];

    $start = $params["start"];
    $limit = $params["limit"];

    $sql = "select id, db_table_name_prefix
            from t_acc_fmt
            where company_id = '%s' and acc_number = '%s' ";
    $data = $db->query($sql, $companyId, $accNumber);
    if (!$data) {
      // 莫名的bug
      return [
        "dataList" => [],
        "totalCount" => 0,
      ];
    }
    $v = $data[0];
    $fmtId = $v["id"];
    $tableName = $v["db_table_name_prefix"] . "_detail";

    $sql = "select db_field_name
            from t_acc_fmt_cols
            where fmt_id = '%s' and sub_acc_level = 1
            limit 1";
    $data = $db->query($sql, $fmtId);
    if (!$data) {
      // 莫名的bug
      return [
        "dataList" => [],
        "totalCount" => 0,
      ];
    }
    $v = $data[0];
    $subAcc01FieldName = $v["db_field_name"];

    $sql = "select db_field_name, voucher_input
            from t_acc_fmt_cols
            where fmt_id = '%s' and sub_acc_level = 2
            limit 1";
    $data = $db->query($sql, $fmtId);
    if (!$data) {
      // 莫名的bug
      return [
        "dataList" => [],
        "totalCount" => 0,
      ];
    }

    $v = $data[0];
    $fieldName = $v["db_field_name"];
    $fieldList = $fieldName;
    $voucherInput = $v["voucher_input"];
    if ($voucherInput == 2) {
      // 码表录入
      $fieldList .= ", {$fieldName}_name";
    }
    $sql = "select distinct {$fieldList} from {$tableName}
            where company_id = '%s' and voucher_year = %d and voucher_month = %d
              and sub_acc_level = 2 and {$subAcc01FieldName} = '%s'
            order by {$fieldName} 
            limit %d , %d";
    $data = $db->query($sql, $companyId, $year, $month, $subAcc01, $start, $limit);
    $result = [
      [
        "code" => "",
        "name" => "[忽略二级子账簿]",
      ],
    ];
    foreach ($data as $v) {
      $n = $v[$fieldName];
      if ($voucherInput == 2) {
        $n = $v[$fieldName] . " - " . $v[$fieldName . "_name"];
      }
      $result[] = [
        "code" => $v[$fieldName],
        "name" => $n,
      ];
    }

    $sql = "select count(distinct {$fieldName}) as cnt from {$tableName}
            where company_id = '%s' and voucher_year = %d and voucher_month = %d
              and sub_acc_level = 2 and {$subAcc01FieldName} = '%s'";
    $data = $db->query($sql, $companyId, $year, $month, $subAcc01);
    $cnt = $data[0]["cnt"];
    return [
      "dataList" => $result,
      "totalCount" => $cnt,
    ];
  }

  /**
   * 查询三级子账簿
   */
  public function querySubAcc03($params)
  {
    $db = $this->db;

    $companyId = $params["companyId"];
    $accNumber = $params["accNumber"];
    $year = $params["year"];
    $month = $params["month"];
    $subAcc02 = $params["subAcc02"];

    $start = $params["start"];
    $limit = $params["limit"];

    $sql = "select id, db_table_name_prefix
            from t_acc_fmt
            where company_id = '%s' and acc_number = '%s' ";
    $data = $db->query($sql, $companyId, $accNumber);
    if (!$data) {
      // 莫名的bug
      return [
        "dataList" => [],
        "totalCount" => 0,
      ];
    }
    $v = $data[0];
    $fmtId = $v["id"];
    $tableName = $v["db_table_name_prefix"] . "_detail";

    $sql = "select db_field_name
            from t_acc_fmt_cols
            where fmt_id = '%s' and sub_acc_level = 2
            limit 1";
    $data = $db->query($sql, $fmtId);
    if (!$data) {
      // 莫名的bug
      return [
        "dataList" => [],
        "totalCount" => 0,
      ];
    }
    $v = $data[0];
    $subAcc01FieldName = $v["db_field_name"];

    $sql = "select db_field_name, voucher_input
            from t_acc_fmt_cols
            where fmt_id = '%s' and sub_acc_level = 3
            limit 1";
    $data = $db->query($sql, $fmtId);
    if (!$data) {
      // 莫名的bug
      return [
        "dataList" => [],
        "totalCount" => 0,
      ];
    }

    $v = $data[0];
    $fieldName = $v["db_field_name"];
    $fieldList = $fieldName;
    $voucherInput = $v["voucher_input"];
    if ($voucherInput == 2) {
      // 码表录入
      $fieldList .= ", {$fieldName}_name";
    }
    $sql = "select distinct {$fieldList} from {$tableName}
            where company_id = '%s' and voucher_year = %d and voucher_month = %d
              and sub_acc_level = 3 and {$subAcc01FieldName} = '%s'
            order by {$fieldName} 
            limit %d , %d";
    $data = $db->query($sql, $companyId, $year, $month, $subAcc02, $start, $limit);
    $result = [
      [
        "code" => "",
        "name" => "[忽略三级子账簿]",
      ],
    ];
    foreach ($data as $v) {
      $n = $v[$fieldName];
      if ($voucherInput == 2) {
        $n = $v[$fieldName] . " - " . $v[$fieldName . "_name"];
      }
      $result[] = [
        "code" => $v[$fieldName],
        "name" => $n,
      ];
    }

    $sql = "select count(distinct {$fieldName}) as cnt from {$tableName}
            where company_id = '%s' and voucher_year = %d and voucher_month = %d
              and sub_acc_level = 3 and {$subAcc01FieldName} = '%s'";
    $data = $db->query($sql, $companyId, $year, $month, $subAcc02);
    $cnt = $data[0]["cnt"];
    return [
      "dataList" => $result,
      "totalCount" => $cnt,
    ];
  }

  /**
   * 查询明细账的账样列
   */
  public function queryFmtColsForDetailAcc($params)
  {
    $db = $this->db;

    $companyId = $params["companyId"];
    $accNumber = $params["accNumber"];
    $subAccLevel = $params["subAccLevel"];

    $result = [];

    $sql = "select id 
            from t_acc_fmt 
            where company_id = '%s' and acc_number = '%s' ";
    $data = $db->query($sql, $companyId, $accNumber);
    if (!$data) {
      return $result;
    }

    $fmtId = $data[0]["id"];

    // 查询最大子账簿数
    $sql = "select count(*) as cnt
            from t_acc_fmt_cols
            where fmt_id = '%s' and sub_acc_level > 0 ";
    $data = $db->query($sql, $fmtId);
    $maxAccLevel = $data[0]["cnt"];
    $isMaxAccLevel = $maxAccLevel == $subAccLevel;
    $ignoreFields = ["x_db_cnt", "x_db_price", "x_cr_cnt", "x_cr_price", "x_balance_cnt", "x_balance_price"];

    $sql = "select id, caption, db_field_name, col_width
            from t_acc_fmt_cols
            where fmt_id = '%s' and show_order > 0 and col_category != 1
              and sub_acc_level <= %d
            order by show_order ";
    $data = $db->query($sql, $fmtId, $subAccLevel);
    foreach ($data as $v) {
      $fieldName = $v["db_field_name"];
      if (!$isMaxAccLevel) {
        if (in_array($fieldName, $ignoreFields)) {
          // 当不是最终子账簿的时候，三栏账中的数量和单价没有意义，就不用显示
          // 原因：数量单价金额三栏账，需要是最明细的子账簿（通常是某个仓库中的某个具体物料，不同物料的数量和单价合计没什么意义）
          continue;
        }
      }

      $result[] = [
        "id" => $v["id"],
        "caption" => $v["caption"],
        "fieldName" => $v["db_field_name"],
        "colWidth" => $v["col_width"],
      ];
    }

    return $result;
  }

  /**
   * 查询明细账数据
   */
  public function queryAccDetailData($params)
  {
    $db = $this->db;

    $companyId = $params["companyId"];
    $accNumber = $params["accNumber"];
    $year = $params["year"];
    $month = $params["month"];
    $subAccLevel = $params["subAccLevel"];
    $subAcc01 = $params["subAcc01"];
    $subAcc02 = $params["subAcc02"];
    $subAcc03 = $params["subAcc03"];

    $start = $params["start"];
    $limit = $params["limit"];

    // 查询物料小数位数
    $bcDAO = new BizConfigDAO($db);
    $dataScale = $bcDAO->getGoodsCountDecNumber($companyId);
    $fmt = "decimal(19, " . $dataScale . ")";

    $sql = "select id, db_table_name_prefix
            from t_acc_fmt
            where company_id = '%s' and acc_number = '%s' ";
    $data = $db->query($sql, $companyId, $accNumber);
    if (!$data) {
      return $this->emptyResult();
    }
    $v = $data[0];
    $fmtId = $v["id"];
    $accTableName = $v["db_table_name_prefix"] . "_detail";

    $listCntField = ["x_db_cnt", "x_cr_cnt", "x_balance_cnt"];

    $sql = "select db_field_name, voucher_input
            from t_acc_fmt_cols
            where fmt_id = '%s' and sub_acc_level <= %d and col_category != 1
            order by show_order";
    $data = $db->query($sql, $fmtId, $subAccLevel);
    $fmtColsList = [];
    $sqlForData = "select acc_flag, ";
    foreach ($data as $i => $v) {
      $f = $v["db_field_name"];
      $voucherInput = $v["voucher_input"];
      $fmtColsList[] = [
        "fieldName" => $f,
        "voucherInput" => $voucherInput,
      ];

      if ($i > 0) {
        $sqlForData .= ",";
      }
      if (in_array($f, $listCntField)) {
        // 数量金额三栏账中的数量格式化成相应的小数位数
        $sqlForData .= "convert({$f}, " . $fmt . ") as {$f}";
      } else {
        $sqlForData .= $f;
      }
      if ($voucherInput == 2) {
        // 码表录入
        $sqlForData .= ", {$f}_name";
      }
    }
    $sqlForData .= " from {$accTableName} ";
    $sqlForData .= " where voucher_year = %d and voucher_month= %d ";
    $sqlForData .= " and company_id = '%s' and sub_acc_level = %d ";

    $queryParams = [];
    $queryParams[] = $year;
    $queryParams[] = $month;
    $queryParams[] = $companyId;
    $queryParams[] = $subAccLevel;

    if ($subAccLevel == 1) {
      // 一级子账簿
      $sql = "select db_field_name
              from t_acc_fmt_cols
              where fmt_id = '%s' and sub_acc_level = 1";
      $data = $db->query($sql, $fmtId);
      if ($data) {
        $f = $data[0]["db_field_name"];
        $sqlForData .= " and {$f} = '%s' ";
        $queryParams[] = $subAcc01;
      }
    } elseif ($subAccLevel == 2) {
      // 二级子账簿 - 有两个SQL键字
      $sql = "select db_field_name
              from t_acc_fmt_cols
              where fmt_id = '%s' and sub_acc_level = 1";
      $data = $db->query($sql, $fmtId);
      if ($data) {
        $f = $data[0]["db_field_name"];
        $sqlForData .= " and {$f} = '%s' ";
        $queryParams[] = $subAcc01;
      }
      $sql = "select db_field_name
              from t_acc_fmt_cols
              where fmt_id = '%s' and sub_acc_level = 2";
      $data = $db->query($sql, $fmtId);
      if ($data) {
        $f = $data[0]["db_field_name"];
        $sqlForData .= " and {$f} = '%s' ";
        $queryParams[] = $subAcc02;
      }
    } elseif ($subAccLevel == 3) {
      // 三级子账簿 - 有三个SQL键字
      $sql = "select db_field_name
              from t_acc_fmt_cols
              where fmt_id = '%s' and sub_acc_level = 1";
      $data = $db->query($sql, $fmtId);
      if ($data) {
        $f = $data[0]["db_field_name"];
        $sqlForData .= " and {$f} = '%s' ";
        $queryParams[] = $subAcc01;
      }
      $sql = "select db_field_name
              from t_acc_fmt_cols
              where fmt_id = '%s' and sub_acc_level = 2";
      $data = $db->query($sql, $fmtId);
      if ($data) {
        $f = $data[0]["db_field_name"];
        $sqlForData .= " and {$f} = '%s' ";
        $queryParams[] = $subAcc02;
      }
      $sql = "select db_field_name
              from t_acc_fmt_cols
              where fmt_id = '%s' and sub_acc_level = 3";
      $data = $db->query($sql, $fmtId);
      if ($data) {
        $f = $data[0]["db_field_name"];
        $sqlForData .= " and {$f} = '%s' ";
        $queryParams[] = $subAcc03;
      }
    }

    $sqlForData .= " order by acc_flag, id";

    $sqlForData .= " limit %d, %d";
    $queryParams[] = $start;
    $queryParams[] = $limit;

    $data = $db->query($sqlForData, $queryParams);

    $result = [];
    foreach ($data as $v) {
      $record = [
        "acc_flag" => $v["acc_flag"],
      ];
      foreach ($fmtColsList as $fc) {
        $fieldName = $fc["fieldName"];
        $voucherInput = $fc["voucherInput"];
        if ($voucherInput == 2) {
          // 码表录入的时候显示格式为：编码 - 名称
          $record[$fieldName] = $v[$fieldName] . " - " . $v[$fieldName . "_name"];
        } else {
          $record[$fieldName] = $v[$fieldName];
        }
      }

      $result[] = $record;
    }

    $sqlForData = "select count(*) as cnt from  {$accTableName}";
    $sqlForData .= " where voucher_year = %d and voucher_month= %d";
    $sqlForData .= " and company_id = '%s' and sub_acc_level = %d";
    $queryParams = [];
    $queryParams[] = $year;
    $queryParams[] = $month;
    $queryParams[] = $companyId;
    $queryParams[] = $subAccLevel;
    if ($subAccLevel == 1) {
      // 一级子账簿
      $sql = "select db_field_name
              from t_acc_fmt_cols
              where fmt_id = '%s' and sub_acc_level = 1";
      $data = $db->query($sql, $fmtId);
      if ($data) {
        $f = $data[0]["db_field_name"];
        $sqlForData .= " and {$f} = '%s' ";
        $queryParams[] = $subAcc01;
      }
    } elseif ($subAccLevel == 2) {
      // 二级子账簿 - 有两个SQL键字
      $sql = "select db_field_name
              from t_acc_fmt_cols
              where fmt_id = '%s' and sub_acc_level = 1";
      $data = $db->query($sql, $fmtId);
      if ($data) {
        $f = $data[0]["db_field_name"];
        $sqlForData .= " and {$f} = '%s' ";
        $queryParams[] = $subAcc01;
      }
      $sql = "select db_field_name
              from t_acc_fmt_cols
              where fmt_id = '%s' and sub_acc_level = 2";
      $data = $db->query($sql, $fmtId);
      if ($data) {
        $f = $data[0]["db_field_name"];
        $sqlForData .= " and {$f} = '%s' ";
        $queryParams[] = $subAcc02;
      }
    } elseif ($subAccLevel == 3) {
      // 三级子账簿 - 有三个SQL键字
      $sql = "select db_field_name
              from t_acc_fmt_cols
              where fmt_id = '%s' and sub_acc_level = 1";
      $data = $db->query($sql, $fmtId);
      if ($data) {
        $f = $data[0]["db_field_name"];
        $sqlForData .= " and {$f} = '%s' ";
        $queryParams[] = $subAcc01;
      }
      $sql = "select db_field_name
              from t_acc_fmt_cols
              where fmt_id = '%s' and sub_acc_level = 2";
      $data = $db->query($sql, $fmtId);
      if ($data) {
        $f = $data[0]["db_field_name"];
        $sqlForData .= " and {$f} = '%s' ";
        $queryParams[] = $subAcc02;
      }
      $sql = "select db_field_name
              from t_acc_fmt_cols
              where fmt_id = '%s' and sub_acc_level = 3";
      $data = $db->query($sql, $fmtId);
      if ($data) {
        $f = $data[0]["db_field_name"];
        $sqlForData .= " and {$f} = '%s' ";
        $queryParams[] = $subAcc03;
      }
    }

    $data = $db->query($sqlForData, $queryParams);
    $cnt = $data[0]["cnt"];

    return [
      "dataList" => $result,
      "totalCount" => $cnt,
    ];
  }

  /**
   * 保存账样列视图
   * 
   */
  public function saveViewLayout(&$params)
  {
    $db = $this->db;

    $companyId = $params["companyId"];
    $sql = "select name from t_org where id = '%s' ";
    $data = $db->query($sql, $companyId);
    if (!$data) {
      return $this->badParam("companyId");
    }
    $companyName = $data[0]["name"];

    $accNumber = $params["accNumber"];

    // 账样字段id，以逗号分隔形成的List
    $idList = $params["idList"];
    // 列宽度，以逗号分隔
    $widthList = $params["widthList"];

    $idArray = explode(",", $idList);
    $widthArray = explode(",", $widthList);
    if (count($idArray) != count($widthArray)) {
      // bug
      return $this->badParam("widthList");
    }

    $sql = "select id
            from t_acc_fmt 
            where company_id = '%s' and acc_number = '%s' ";
    $data = $db->query($sql, $companyId, $accNumber);
    if (!$data) {
      return $this->bad("账簿不存在");
    }
    $v = $data[0];
    $fmtId = $v["id"];

    foreach ($idArray as $i => $colId) {
      $showOrder = $i + 1;

      $width = $widthArray[$i];

      $sql = "update t_acc_fmt_cols
              set show_order = %d, col_width = %d
              where fmt_id = '%s' and db_field_name = '%s' ";
      $rc = $db->execute($sql, $showOrder, $width, $fmtId, $colId);
      if ($rc === false) {
        return $this->sqlError(__METHOD__, __LINE__);
      }
    }

    // 操作成功
    $params["log"] = "保存[{$companyName}]账簿码为[{$accNumber}]的视图布局";
    return null;
  }

  /**
   * 查询一级科目列表
   */
  public function subjectList($params)
  {
    $db = $this->db;

    $companyId = $params["companyId"];
    $subjectCode = $params["subjectCode"];

    $sql = "select id, code, name
            from t_subject
            where company_id = '%s' and parent_id is null and inited = 1";
    $queryParams = [];
    $queryParams[] = $companyId;
    if ($subjectCode) {
      $sql .= " and code like '%s' ";
      $queryParams[] = "{$subjectCode}%";
    }
    $sql .= " order by code";
    $data = $db->query($sql, $queryParams);

    $result = [];

    foreach ($data as $v) {
      $result[] = [
        "id" => $v["id"],
        "code" => $v["code"],
        "name" => $v["name"],
      ];
    }
    return $result;
  }

  /**
   * 查询总分类账数据
   */
  public function queryAccGlData($params)
  {
    $db = $this->db;

    $companyId = $params["companyId"];
    $subjectCode = $params["subjectCode"];
    $year = $params["year"];
    $month = $params["month"];

    $sql = "select gl_table_name, name
            from t_subject
            where company_id = '%s' and code = '%s' ";

    $data = $db->query($sql, $companyId, $subjectCode);
    if (!$data) {
      // 莫名的bug
      return [];
    }

    $subjectName = $data[0]["name"];
    $tableName = $data[0]["gl_table_name"];

    $sql = "select voucher_dt, acc_db, acc_cr, acc_balance, acc_balance_dbcr, voucher_summary,
              acc_flag
            from {$tableName}
            where voucher_year = %d and voucher_month = %d
              and company_id = '%s'
            order by id";
    $data = $db->query($sql, $year, $month, $companyId);

    $result = [];

    foreach ($data as $v) {
      $result[] = [
        "code" => $subjectCode,
        "name" => $subjectName,
        "voucherDT" => $v["voucher_dt"],
        "debit" => $v["acc_db"],
        "credit" => $v["acc_cr"],
        "balance" => $v["acc_balance"],
        "balanceDir" => $v["acc_balance_dbcr"],
        "voucherSummary" => $v["voucher_summary"],
        "accFlag" => $v["acc_flag"],
      ];
    }

    return $result;
  }

  /**
   * 查询凭证id
   */
  public function queryVoucherId($params)
  {
    $db = $this->db;

    $companyId = $params["companyId"];
    $year = $params["year"];
    $month = $params["month"];
    $word = $params["word"];
    $num = $params["num"];

    $sql = "select id
            from t_voucher
            where company_id = '%s' and v_year = %d
              and v_month = %d and v_word = '%s' and v_ref = %d";
    $data = $db->query($sql, $companyId, $year, $month, $word, $num);
    if ($data) {
      return [
        "success" => true,
        "id" => $data[0]["id"],
      ];
    } else {
      return [
        "success" => false,
      ];
    }
  }
}
